<!DOCTYPE html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>ImportExcelFormat</title>
<style>
#drop{
	border: 2px dashed #bbb;
    -moz-border-radius: 5px;
    -webkit-border-radius: 5px;
    border-radius: 5px;
    text-align: center;
    font: 13pt bold,"Vollkorn";
    color: #bbb;
    width: 251px;
    display: inline-block;
    vertical-align: sub;
	margin-left: 18px;
	margin-top: 20px;
}
#b64data{
	width:100%;
}
#htmlout>div:nth-of-type(1){
	height: 50px;
	line-height: 50px;
}
ul{
	padding: 0;
	margin-left: 20px;
	max-width: 90%;
}
li{
	list-style-type: none;
    height: 37px;
    line-height: 37px;
	display: flex;
}
li span{
	display: inline-block;
	width: 141px;
	flex: 1;
    vertical-align: top;
    text-align: center;
	border: 1px solid #ddd;
	height: 37px;
	overflow: hidden;
	text-overflow:ellipsis;
	white-space: nowrap;
	margin-left: -1px;
}
li span input{
	width: 100%;
	height: 100%;
    vertical-align: inherit;
	border: none;
	overflow: hidden;
	text-overflow:ellipsis;
}
li span select{
	width: 100%;
	border: none;
	height: 34px;
}
#xlf{
	display: none;
}
.select_file{
	padding: 11px;
	color: #a39f9f;
    cursor: pointer;
	border-radius: 5px;
}
li{
	margin-top: 1px;
}
li:nth-last-of-type(1) span{
	border-bottom: 1px solid #ddd;
}
li:not(:first-child):hover span{
	background: #faf6f6;
}
li:not(:first-child):hover input{
	background: #faf6f6;
}
li:not(:first-child):hover select{
	background: #faf6f6;
}
.title{
	margin-left: 20px;
}
.btn{
    background: #ddd;
    color: #000;
    margin-top: 20px;
    margin-left: 20px;
}
.btn.success{
    background: #008000;
    color:#fff;
    margin-top: 20px;
    margin-left: 20px;
}
.del{
	background: #f00;
    border: none;
    color: #fff;
    padding: 0px 17px;
    height: 80%;
    border-radius: 3px;
}
a { text-decoration: none }
</style>
</head>
<body>
<div id="drop"><input type="file" name="xlfile" id="xlf" /><label for="xlf" class="select_file">把表拖到这里,或者点击这里</label></div>
<div id="htmlout"></div>
<br />
<!-- uncomment the next line here and in xlsxworker.js for encoding support -->
<script src="js/jquery.min.js"></script>
<script src="js/xlsx.js"></script>
<script>
//去掉首尾两端的空白符
function trim(v){
    if(v==undefined)v='';
    return v.replace(/(^\s*)|(\s*$)/g, "");
}
/*jshint browser:true */
/* eslint-env browser */
/*global Uint8Array, console */
/*global XLSX */
/* exported b64it, setfmt */
/* eslint no-use-before-define:0 */
var X = XLSX;
var global_wb;

var process_wb = (function() {
	var OUT = document.getElementById('out');
	var HTMLOUT = document.getElementById('htmlout');

	var to_json = function to_json(workbook) {
		var result = {};
		workbook.SheetNames.forEach(function(sheetName) {
			var roa = X.utils.sheet_to_json(workbook.Sheets[sheetName], {header:1});
			if(roa.length) result[sheetName] = roa;
		});
		return JSON.stringify(result, 2, 2);
	};
	return function (wb) {
		global_wb = wb;
		var output = "";
		output = to_json(wb);
		drawExcel(output)
	};
})();

var do_file = (function() {
	var rABS = typeof FileReader !== "undefined" && (FileReader.prototype||{}).readAsBinaryString;
	var domrabs = true;
	if(!rABS) domrabs.disabled = !(domrabs.checked = false);

	var use_worker = typeof Worker !== 'undefined';
	var domwork = true;
	if(!use_worker) domwork.disabled = !(domwork.checked = false);

	var xw = function xw(data, cb) {
		var worker = new Worker(XW.worker);
		worker.onmessage = function(e) {
			switch(e.data.t) {
				case 'ready': break;
				case 'e': console.error(e.data.d); break;
				case XW.msg: cb(JSON.parse(e.data.d)); break;
			}
		};
		worker.postMessage({d:data,b:rABS?'binary':'array'});
	};

	return function (files) {
		rABS = domrabs.checked;
		use_worker = domwork.checked;
		var f = files[0];
		if(f!=undefined){
			var reader = new FileReader();
			reader.onload = function(e) {
				var data = e.target.result;
				if(!rABS) data = new Uint8Array(data);
				if(use_worker) xw(data, process_wb);
				else process_wb(X.read(data, {type: rABS ? 'binary' : 'array'}));
			};
			if(rABS) reader.readAsBinaryString(f);
			else reader.readAsArrayBuffer(f);
		}
	};
})();
	//拖动
	var drop = document.getElementById('drop');
	drop.addEventListener('dragenter', handleDragover, false);
	drop.addEventListener('dragover', handleDragover, false);
	drop.addEventListener('drop', handleDrop, false);
	function handleDrop(e) {
		e.stopPropagation();
		e.preventDefault();
		do_file(e.dataTransfer.files);
	}
	function handleDragover(e) {
		e.stopPropagation();
		e.preventDefault();
		e.dataTransfer.dropEffect = 'copy';
	}
	//选择文件
	$("#xlf").change(function(e){
		do_file(e.target.files);
	})
</script>
<script>
	drawExcel()
	function drawExcel(v){
//测试数据
		var str=`{
  "店铺": [
    [
      "店铺名称*",
      "地址*",
      "联系人*",
      "联系电话*",
      "训练场地",
      "描述"
    ],
    [
      null,
      "古冶区林西体育馆二层",
      "张淑敏",
      "185829999991",
      "修炼厅1"
    ],
    [
      "健体馆",
      "古冶区林西体育馆二层",
      "张淑敏",
      "18582999999",
      "修炼厅1"
    ],
    [
      "健体馆1",
      "古冶区林西体育馆二层",
      "张淑敏",
      "18582999999",
      "修炼厅1"
    ],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    [],
    []
  ]
}`;
		// var str=v; 自己开发的时候可以把这里注释给去掉，把上面的注释开启
		/*
			String: obj[excelData] 表格数据
			Array:type[check] 校验规则，可以自定义,
			Fn:type[format] 格式话对应的列，一般为生成select
			String:bgColor 默认错误提示颜色
			Fn:validateRules["isTel"] 为自定义的校验规则
		*/
		new excelBuild({
			excelData:str,
			type:{
				0:{
					check:['require'],
					format:function(v){
						var $sel=$("<span><select><option value='1'>1</option><option value='1'>1</option><option value='1'>1</option><select></span>");
						v.append($sel);
						ret=$sel.find("select").val();
					}
				},
				1:{
					check:['require'],
				},
				2:{
					check:['require'],
				},
				3:{
					check:['require','isPhone'],
				},
				4:{
					check:[],
				},
				5:{
					check:[],
				}
			},
			bgColor:"#f4afafd4",
			validateRules:{
				"isTel":function(v){
					var re=/(\d{3,4}\-)?\d{7,8}/;
					if(re.test(v)){
						return true
					}else{
						return false
					}
				}
			}
		})
	}
	
	function excelBuild(obj){
		var _build={
			bgColor:"#f4afafd4",
		};
		for(var key in obj){
			_build[key]=obj[key];
		}
		var type=_build.type,bgColor=_build.bgColor,validateRules=_build.validateRules,excelData=_build.excelData;
		if(type){
			for(var i in type){
				if(type[i]['check'].length!=0){
					type[i]['adopt']=[];
				}
			}
		}
		this.init=function(obj){
			var _initData={};
			for(var key in obj){
				_initData[key]=obj[key];
			}
			var that=this;
			var excelData=_initData.excelData,type=_initData.type,validateRules=_initData.validateRules;
			$("#htmlout").html("")
			if(excelData){
				var obj=JSON.parse(excelData);
			}else{
				return
			}
			
			//i 为表名 a为行 b为列
			for(var i in obj){
				$("#htmlout").prepend("<ul></ul>")
				for(var a=0;a<obj[i][0].length;a++){
					if(obj[i][0]&&obj[i][a].length!=0){
						var k=parseInt(a)==0?"":parseInt(a);
						$("#htmlout ul").append("<li><span>"+k+"</span></li>")
						var len=obj[i][0].length+1;
						//len列数
						for(var b=0;b<len;b++)
						{	
							if(obj[i]&&obj[i][a]&&obj[i][a][b]==null){
								if(b==obj[i][0].length&&a!=0){
									// "+a+","+JSON.stringify(type)+"
									var btn1;
									(function(){
										// 保存变量a,把通过闭包的方式让点击的时候拿到变量
										var t=a;
										return function(){
											 btn1=$("<span><button class='del'>删除</button></span>").click(function(){
												that.del({
													a:t,
													type:JSON.stringify(type),
													validateRules:validateRules
												})
											});
											$("#htmlout ul li").eq(a).append(btn1);
										}
									}())()
								}else{
									//第n行，所有报错的单元格,排除掉有格式的单元格
									if(b!=undefined&&type[b]&&type[b]["format"]!=undefined){
										(function(){
											var obj1=$("#htmlout ul li").eq(a)
											type[b]["format"](obj1)
										}())	
									}else{
										$("#htmlout ul li").eq(a).append("<span><input type='text' value='' _inx="+b+" _iny="+a+"></span>")
									}
								}
							}else{
								//有数据的通过,不报错的通过
								//第一行
								if(a==0){
									// console.log(obj[i][a][b])
									$("#htmlout ul li").eq(a).append("<span _inx="+b+" _iny="+a+">"+trim(obj[i][a][b])+"</span>")
								}else{
									if(b!=undefined&&type[b]&&type[b]["format"]!=undefined){
										(function(){
											var obj1=$("#htmlout ul li").eq(a)
											type[b]["format"](obj1)
										}())	
									}
									else{
										if(a!=undefined&&obj[i]!=undefined){
											$("#htmlout ul li").eq(a).append("<span><input type='text' _inx="+b+" _iny="+a+" value="+trim(obj[i][a][b])+"></span>")
										}
									}
									// $("#htmlout ul li").eq(a).append("<span><input type='text' _inx="+b+" _iny="+a+" value="+trim(obj[i][a][b])+"></span>")
									// console.log(type[b])
									
								}
							}
						}
					}
				}
				$("#htmlout").prepend("<div class='title'>"+i+"表</div>");
			}
			$("#htmlout").append("<button class='btn' disabled='disabled'>提交</button>")
		};
		this.checkAdult=function(val){
			return val==false
		},
		this.del=function(obj){
			var _default={};
			for(var key in obj){
				_default[key]=obj[key];
			}
			var type=_default.type,bgColor=_default.bgColor,validateRules=_default.validateRules,a=_default.a;
			for(var i=0;i<$("#htmlout ul li").length;i++){
				if($("#htmlout ul li").eq(i).find("span").eq(0).text()==a){
					$("#htmlout ul li").eq(i).remove()
				}
			}
			this.check({type:type,bgColor:bgColor,validateRules:validateRules})
			this.submitAdopt (type)
		}
		this.check=function(obj){
			var _default={
				type:{},
				bgColor:"",
				validateRules:{
					"require":function(v){
						if(v.length!=0){
							return true
						}else{
							return false
						}
					},
					"isPhone":function(v){
						var re=/^[1][3,4,5,7,8][0-9]{9}$/
						if(re.test(v)){
							return true
						}else{
							return false
						}
					}
				}
			};
			var that=this;
			for(var key in obj){
				if(key!='test'&&key!="validateRules"){
					_default[key]=obj[key];
				}else if(key=='validateRules'){	
					var validateRules=Object.assign(_default[key],obj[key]);
				}
			
			}
			var type=_default.type,bgColor=_default.bgColor,validateRules=validateRules;
			for(var i=0;i<$("#htmlout ul li").length;i++){
				if(i!=0){
					//初始验证
					for(var b=0;b<$("#htmlout ul li").eq(i).find("span").length-2;b++){
						var testValue=$("#htmlout ul li").eq(i).find("span").eq(b+1).find("input").val();
						var obj=$("#htmlout ul li").eq(i).find("span").eq(b+1).find("input");
						if(type[b].check&&type[b].check.length&&type[b].adopt!=undefined){
							var _adopt=false;
							for(var c=0;c<type[b].check.length;c++){
								if(type[b].check[c].length!=0){
									try {
										if(validateRules[type[b].check[c]]&&type[b]['format']==undefined){
											if((validateRules[type[b].check[c]])(testValue)){
												obj.css("background","#fff0");
												_adopt=true;
											}else{
												obj.css("background",bgColor);
												_adopt=false;
											}
										}
									} catch (error) {
										console.error("validateRules 没有找到校验规则:"+type[b].check[c]+"")
									}
									
								}
							}
							type[b].adopt.push(_adopt)
						}
						//实时验证
						obj.on("input",function(){
							testValue=$(this).val();
							var b=parseInt($(this).attr("_inx"));//排除第一列
							var d=parseInt($(this).attr("_iny"))-1;//-1排除第一行
							if(type[b].check&&type[b].check.length){
								//单列验证
								var _adopt=false;
								for(var c=0;c<type[b].check.length;c++){
									if(type[b].check[c]!=null){
										if((validateRules[type[b].check[c]])(testValue)&&type[b]['format']==undefined){
											$(this).css("background","#fff0");
											_adopt=true;
										}else{
											$(this).css("background",bgColor);
											_adopt=false;
										}
									}
								}
								type[b].adopt.splice(d,1,_adopt)
							}
							//提交按钮实时验证
							that.submitAdopt (type)
						})
						
					}	
				}
			}
		};
		//校验通过
		this.submitAdopt=function(type){
			var adoptArr=[],adopt=false;
			for(var e=0;e<$("#htmlout ul li").eq(0).find("span").length;e++){
				if(type[e]&&type[e].adopt!=undefined&&type[e]['format']==undefined){
					if(type[e].adopt.some(this.checkAdult)){
						adopt=false
					}else{
						adopt=true
					}
					adoptArr.push(adopt)
				}
			}
			if(adoptArr.some(this.checkAdult)){
				//禁止提交
				$(".btn").attr("disabled",true)
			}else{
				//允许提交
				this.submitFn()
			}
		}
		this.submitFn=function(){
			var arr=[];
			for(var i=0;i<$("#htmlout ul li").length;i++){
				arr.push([])
				var len=$("#htmlout ul li").eq(i).find("span").length-1;//排除最后一个按钮和第一个单元格
				for(var b=0;b<len;b++){
					if(b!=0){
						if($("#htmlout ul li").eq(i).find("span").eq(b).find("input").length!=0){
							arr[i].push(trim($("#htmlout ul li").eq(i).find("span").eq(b).find("input").val()))
						}else if($("#htmlout ul li").eq(i).find("span").eq(b).find("select").length!=0){
							arr[i].push(trim($("#htmlout ul li").eq(i).find("span").eq(b).find("select").val()))
						}
						else if($("#htmlout ul li").eq(i).find("span").eq(b).find("input").length==0){
							arr[i].push(trim($("#htmlout ul li").eq(i).find("span").eq(b).text()))
						}
						
					}
				}
			}
			if(arr[1]!=undefined){
				$(".btn").attr("disabled",false)
				return arr;
			}else{
				$(".btn").attr("disabled",true)
			}
		
		}
		this.init({
			excelData:excelData,
			type:type,
			validateRules:validateRules
		});
		this.check({
			type:type,
			bgColor:bgColor,
			validateRules:validateRules
		})
		$(".btn").click(function(){
			console.log(this.submitFn())
		}.bind(this))
	}
	
	
</script>
</body>
</html>
